1. Field of the Invention
The present invention relates to database utility software, and more particularly to a system and method for controlling free space distribution by key range within a database.
2. Description of the Related Art
Database utility software programs that build table spaces and indexes (e.g., database reorganization, database loading, database recovery, among others) typically apply free space parameters evenly across the entire table space and index. This approach of even distribution may lead to an inefficient use of Direct Access Storage Device (DASD) resources (e.g., allocating too much free space in a section of the table space or index that does not require free space to accommodate growth, or not allocating enough free space in a section of the table space or index that experiences high levels of growth). Unneeded free space allocated to a section of the table space or index may have an impact on performance since more reads may be required to retrieve a given amount of data. Furthermore, disorganization may occur when DB2 attempts to insert or update data within a section of the table space or index that has insufficient free space to accommodate growth.
A table space may contain multiple tables. In this situation, DB2 does not provide the user with the capability to define free space attributes for each table within a given table space. Free space attributes may only be defined for the table space as a whole. Therefore, it is desirable to be able to apply free space to accommodate the free space requirements of each individual table within a multi-table table space.
The current version of REORG PLUS for DB2, a product of BMC Software, Inc., applies free space to a table space during the process of calculating new row identifiers (RIDs) for table space rows during the unload phases. This process is described more fully in U.S. Pat. No. 5,222,235, titled “Databases system for permitting concurrent indexing and reloading of data by early simulating the reload process to determine final locations of the data”, whose inventors are Thomas E. Hintz and William R. Cunningham, and which issued on Jun. 22, 1993. U.S. Pat. No. 5,222,235 is hereby incorporated by reference in its entirety as though fully and completely set forth herein.
The RID for each row contains the page number of the page where the row will reside after reorganization. The new RID is written with its corresponding row data to an unload file for processing during the reload phase. During the RID calculation process, free space parameters are applied to keep a portion of each data page free (PCTFREE or percent free) or to insert free pages (FREEPAGE or free page) between data pages. It is noted that the free space parameters are applied at a table space level, rather than at a finer level of granularity within the table space.
The current version of REORG PLUS for DB2 applies free space to indexes in a similar manner. During the unload phase, after the new RID for each row in the file page set has been calculated, REORG PLUS for DB2 extracts the keys for each secondary index and writes them with the new RID to an index work file. During the reload phase, the index work file is sorted by index key. After the sort is complete, free space parameters are applied as index pages and are reloaded with key and RID pairs. The clustering index is rebuilt in a similar manner.
It is desirable that free space settings or parameters allow more granularity (i.e., allow user-defined sub-sections, or key ranges, within a table space or index to be individually controllable through user-defined free space settings or parameters). Thus, using this finer level of granularity than currently available, the user may be able to reduce free space in key ranges of a table or index that do not require free space for insert or update activity (e.g., read-only data), and/or increase free space in key ranges of a table or index that are expected to require new growth (e.g., new database records inserted, existing database records updated). For the foregoing reasons, there is a need for a system and method for controlling free space distribution by key range within a database such that user control of free space settings or parameters within a table or index is achievable.